<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class getdata extends CI_Controller {
	/*
	This controller is used for geting data from database.
	INPUT JSON - table name, dimensions, measures
	
	OUTPUT table
	*/
	public function index()
	{
		$this->load->database();
		
		$a = file_get_contents('php://input');
		$data = json_decode($a);
		
		$tableData = array();
		
		// Get Table name
		if (isset($data->table))
		{
			$table = mysql_real_escape_string($data->table);
			
			$query = $this->db->query("select COLUMN_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '" . mysql_escape_string($table) . "' AND NOT CONSTRAINT_NAME = 'PRIMARY'");
			
			foreach( $query->result() as $row)
			{
				$tableData[$row->COLUMN_NAME] = array("table" => $row->REFERENCED_TABLE_NAME,  "column" => $row->REFERENCED_COLUMN_NAME);
				
				//array_push($tableData, array($row->COLUMN_NAME => array($row->REFERENCED_TABLE_NAME, $row->REFERENCED_COLUMN_NAME)));
			}
			
		}
		else
		{
			return;
		}
		
		//Get dimensions
		if (isset($data->dimensions))
		{
			$dimensions = $data->dimensions;
		}
		else
		{
			$dimensions = Array();
		}
		
		//Get measures
		if (isset($data->measures))
		{
			$measures = $data->measures;
		}
		else
		{
			$measures = Array();
		}
		
		$selectPart = "SELECT ";
		$selectRowsCount = 0;
		$groupByRowsCount = 0;
		
		$fromPart = " FROM " . $table;
		
		$wherePart = " WHERE ";
		
		$groupByPart = " GROUP BY ";
		
        $fromDuplicate = Array();
        
		//Foreach DIMENSION
		foreach($dimensions as $dimension)
		{
			//$dimension = $dimension->dimension;
			
			if ($selectRowsCount != 0)
			{
				$selectPart .= ", ";
                $groupByPart .= ",";
				$wherePart .= " AND ";
			}
			
			$selectPart .= $tableData[$dimension->sqlname]['table'] . "." . $this->db->escape_str($dimension->dimensionsqlname);
			
			$groupByPart .= $this->db->escape_str($dimension->dimensionsqlname);
			
            if (! isset( $fromDuplicate[$tableData[$dimension->sqlname]['table']]))
            {
			    $fromPart .= ", " . $tableData[$dimension->sqlname]['table'];
                $fromDuplicate[ $tableData[$dimension->sqlname]['table']] = true;
            }
			$wherePart .= $tableData[$dimension->sqlname]['table'] . "." . $tableData[$dimension->sqlname]['column'] . " = " . $table . "." . $this->db->escape_str($dimension->sqlname);
			
			$groupByRowsCount++;
			$selectRowsCount++;
		}
		
		//Foreach MEASURE
		foreach($measures as $measure)
		{
			//$measure = $measure->measure;
			
			if ( $selectRowsCount != 0 || $groupByRowsCount != 0)
			{
				$selectPart .=  ", ";
			}
			$selectPart .= $this->db->escape_str($measure->agregatefunction) . '(';
			$selectPart .= $this->db->escape_str($measure->sqlname) . ')';
			
			$groupByRowsCount++;
		}
		
		$queryString = $selectPart . $fromPart . $wherePart . $groupByPart;
		//echo $queryString;
		
		//echo $queryString;
		
		$query2 = $this->db->query($queryString);
		
		$result = array();
		
		//var_dump($query2->result());
		
		$keys = array();
		
		foreach( $query2->result() as $row1)
		{
			$keys = array_keys(get_object_vars($row1));
			array_push($result, $row1);
			
		}
		echo( json_encode(array("keys" => $keys, "results" => $result)));
		//$this->load->view('welcome_message');
	}
}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */
?>